package co.gov.medellin.ssti.sub.paoyer.administration.dao;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Vector;
import java.sql.PreparedStatement;

import co.gov.medellin.ssti.sub.paoyer.administration.model.vo.UserVO;

/**
 * DAO para crear, consultar y actualizar registros en la tabla USUARIOS.
 * 
 * @author Nicolas.Ceron@consultsoft.com
 * @version 1.0
 */

public class UserDAO {

	/**
	 * Conexion a la base de datos.
	 */
	private Connection conn = null;

	/**
	 * Constructor que inicializa la conexion con la base de datos.
	 * 
	 * @param conn
	 *            Conexion con la base de datos.
	 */

	public UserDAO(Connection conn) {
		this.conn = conn;
	}

	/**
	 * TABLA TCS_USUARIO
	 */

	/*
	 * public UserVO authenticateUser(UserVO criterea) throws SQLException {
	 * 
	 * String query = "SELECT usua.* FROM tcs_usuario usua WHERE usua.nombre='"
	 * + criterea.getName()+"' AND usua.campo_a1='"+criterea.getPassword()+"'";
	 * Statement stm = null; ResultSet rst = null; stm = conn.createStatement();
	 * rst = stm.executeQuery(query); UserVO user = null; if( rst.next() ) {
	 * user = new UserVO(); user.setCode(new Long(rst.getLong( "codigo")));
	 * user.setName(rst.getString( "nombre"));
	 * user.setDescription(rst.getString( "descripcion"));
	 * user.setPassword(rst.getString( "campo_a1")); } return user; }
	 */

	public UserVO authenticateUser(UserVO criterea) throws SQLException {

		String query = "SELECT usua.codigo usua_codigo, usua.nombre usua_nombre, "
				+ "usua.descripcion usua_descripcion, usua.campo_a1 usua_password, "
				+ "null autr_codigo, gus.descripcion gus_descripcion, gus.codigo gus_codigo, usua.campo_n1 "
				+ "FROM tcs_usuario usua, tcs_grupo_usuario gus "
				+ "WHERE usua.nombre = '"
				+ criterea.getName()
				+ "' "
				+ "AND gus.codigo= usua.gus_codigo";

		Statement stm = null;
		ResultSet rst = null;
		stm = conn.createStatement();
		rst = stm.executeQuery(query);
		UserVO user = null;
		if (rst.next()) {
			user = new UserVO();
			user.setCode(new Long(rst.getLong("usua_codigo")));
			user.setName(rst.getString("usua_nombre"));
			user.setDescription(rst.getString("usua_descripcion"));
			user.setPassword(rst.getString("usua_password"));
			user.setUserGroupCode(new Long(rst.getLong("gus_codigo")));
			user.setUserGroupName(rst.getString("gus_descripcion"));
			user.setCodeArea(new Long(rst.getLong("campo_n1")));
		}

		// user = new UserVO();
		// user.setCode(new Long(33));
		// user.setName("andres");
		// user.setDescription("Administrador");
		// user.setPassword("Administrador");
		// user.setUserGroupCode(new Long(1));
		// user.setUserGroupName("Administrador");
		// user.setCodeArea(new Long(1));
		//

		return user;
	}

	public Collection<UserVO> findUserByCriterea(UserVO criterea)
			throws SQLException {
		String query = "SELECT usu.codigo,"
				+ "       usu.nombre,"
				+ "       to_char(usu.desde_vigencia,'yyyy-mm-dd') dvig,"
				+ "       to_char(usu.hasta_vigencia,'yyyy-mm-dd') hvig,"
				+ "       usu.vigencia, "
				+ "       usu.campo_a1, "
				+ "       nvl(usu.campo_n1,0)campo_n1, "
				+ "       usu.fecha_crea,"
				+ "       usu.usuario_crea,"
				+ "       usu.terminal_crea,"
				+ "       usu.fecha_modifica,"
				+ "       usu.usuario_modifica,"
				+ "       usu.terminal_modifica,"
				+ "       usu.descripcion,"
				+ "       usu.gus_codigo,"
				+ "       gus.nombre userGroupName,"
				+ "       area.NOMBRE areaName "
				+ " FROM tcs_usuario usu, tcs_grupo_usuario gus, beneficios.TCS_BEN_AREA area";

		String filters = "";
		// Filtro por Nombre
		if ((criterea.getName() != null)
				&& !(criterea.getName().equalsIgnoreCase(""))) {
			filters = filters + " (UPPER(usu.nombre) LIKE '%"
					+ criterea.getName().toUpperCase()
					+ "%' OR UPPER(usu.descripcion) LIKE '%"
					+ criterea.getDescription().toUpperCase() + "%') ";
		}

		/*
		 * // Filtro por Descripcion if ( (criterea.getDescription() != null) &&
		 * !(criterea.getDescription().equalsIgnoreCase("")) ) { if (
		 * filters.equalsIgnoreCase("") ) { filters = filters +
		 * " UPPER(usu.descripcion) LIKE '%" +
		 * criterea.getDescription().toUpperCase() + "%' "; } else { filters =
		 * filters + " AND UPPER(usu.descripcion) LIKE '%" +
		 * criterea.getDescription().toUpperCase() + "%' "; } }
		 */

		// Filtro por Codigo
		if ((criterea.getCode() != null)
				&& !(criterea.getCode().longValue() == 0)) {
			if (filters.equalsIgnoreCase("")) {
				filters = filters + " usu.codigo = " + criterea.getCode();
			} else {
				filters = filters + " AND usu.codigo = " + criterea.getCode();
			}
		}

		// filtro por codigo area
		if ((criterea.getCodeArea() != null)
				&& !(criterea.getCodeArea().longValue() == 0)) {
			if (filters.equalsIgnoreCase("")) {
				filters = filters + " area.codigo = " + criterea.getCodeArea();
			} else {
				filters = filters + " AND area.codigo = "
						+ criterea.getCodeArea();
			}
		}

		if ((criterea.getUserGroupCode() != null)
				&& !(criterea.getUserGroupCode().longValue() == 0)) {
			if (filters.equalsIgnoreCase("")) {
				filters = filters + " gus.codigo = "
						+ criterea.getUserGroupCode();
			} else {
				if (criterea.getUserGroupCode() != null) {
					filters = filters + " AND gus.codigo = "
							+ criterea.getUserGroupCode();
				}

			}
		}

		// Si hay filtros, entonces armar la clausula WHERE
		if (!filters.equalsIgnoreCase("")) {
			query = query
					+ " where usu.campo_a1 IS NULL and gus.campo_a1 IS NULL and usu.gus_codigo = gus.codigo and area.codigo (+)= usu.campo_n1 and usu.VIGENCIA = 'S' and "
					+ filters;
		} else {
			query = query
					+ " where usu.campo_a1 IS NULL and gus.campo_a1 IS NULL and usu.gus_codigo = gus.codigo and area.codigo (+)= usu.campo_n1 and usu.VIGENCIA = 'S' ";
		}

		// System.out.println(query);
		// String striquery = query;
		Statement stm = null;
		ResultSet rst = null;
		ArrayList<UserVO> users = new ArrayList<UserVO>();
		stm = conn.createStatement();
		rst = stm.executeQuery(query);

		while (rst.next()) {
			UserVO user = new UserVO();
			user.setCode(new Long(rst.getLong("codigo")));
			user.setName(rst.getString("nombre"));
			user.setDescription(rst.getString("descripcion"));
			user.setPassword(rst.getString("campo_a1"));
			user.setCodeArea(new Long(rst.getLong("campo_n1")));
			user.setEffectiveFrom(rst.getString("dvig"));
			user.setEffectiveUntil(rst.getString("hvig"));
			user.setUse(rst.getString("vigencia"));
			user.setUserGroupCode(new Long(rst.getLong("gus_codigo")));
			user.setCreationUser(rst.getString("usuario_crea"));
			user.setCreationTerminal(rst.getString("terminal_crea"));
			user.setCreationDate(rst.getString("fecha_crea"));
			user.setUpdateUser(rst.getString("usuario_modifica"));
			user.setUpdateTerminal(rst.getString("terminal_modifica"));
			user.setUpdateDate(rst.getString("fecha_modifica"));
			user.setUserGroupName(rst.getString("userGroupName"));
			user.setNameArea(rst.getString("areaName"));
			users.add(user);
		}
		rst.close();
		stm.close();
		return users;
	}

	public Collection<UserVO> findUserByCritereaAdmin(UserVO criterea)
			throws SQLException {
		String query = "SELECT usu.codigo,"
				+ "       usu.nombre,"
				+ "       to_char(usu.desde_vigencia,'yyyy-mm-dd') dvig,"
				+ "       to_char(usu.hasta_vigencia,'yyyy-mm-dd') hvig,"
				+ "       usu.vigencia, "
				+ "       usu.campo_a1, "
				+ "       nvl(usu.campo_n1,0)campo_n1, "
				+ "       usu.fecha_crea,"
				+ "       usu.usuario_crea,"
				+ "       usu.terminal_crea,"
				+ "       usu.fecha_modifica,"
				+ "       usu.usuario_modifica,"
				+ "       usu.terminal_modifica,"
				+ "       usu.descripcion,"
				+ "       usu.gus_codigo,"
				+ "       gus.nombre userGroupName,"
				+ "       area.NOMBRE areaName "
				+ " FROM tcs_usuario usu, tcs_grupo_usuario gus, beneficios.TCS_BEN_AREA area";

		String filters = "";
		// Filtro por Nombre
		if ((criterea.getName() != null)
				&& !(criterea.getName().equalsIgnoreCase(""))) {
			filters = filters + " (UPPER(usu.nombre) LIKE '%"
					+ criterea.getName().toUpperCase()
					+ "%' OR UPPER(usu.descripcion) LIKE '%"
					+ criterea.getDescription().toUpperCase() + "%') ";
		}

		// Filtro por Codigo
		if ((criterea.getCode() != null)
				&& !(criterea.getCode().longValue() == 0)) {
			if (filters.equalsIgnoreCase("")) {
				filters = filters + " usu.codigo = " + criterea.getCode();
			} else {
				filters = filters + " AND usu.codigo = " + criterea.getCode();
			}
		}

		// filtro por codigo area
		if ((criterea.getCodeArea() != null)
				&& !(criterea.getCodeArea().longValue() == 0)) {
			if (filters.equalsIgnoreCase("")) {
				filters = filters + " area.codigo = " + criterea.getCodeArea();
			} else {
				filters = filters + " AND area.codigo = "
						+ criterea.getCodeArea();
			}
		}

		if ((criterea.getUserGroupCode() != null)
				&& !(criterea.getUserGroupCode().longValue() == 0)) {
			if (filters.equalsIgnoreCase("")) {
				filters = filters + " gus.codigo = "
						+ criterea.getUserGroupCode();
			} else {
				if (criterea.getUserGroupCode() != null) {
					filters = filters + " AND gus.codigo = "
							+ criterea.getUserGroupCode();
				}

			}
		}

		// Si hay filtros, entonces armar la clausula WHERE
		if (!filters.equalsIgnoreCase("")) {
			query = query
					+ " where usu.campo_a1 IS NULL and gus.campo_a1 IS NULL and usu.gus_codigo = gus.codigo and area.codigo (+)= usu.campo_n1 and "
					+ filters;
		} else {
			query = query
					+ " where usu.campo_a1 IS NULL and gus.campo_a1 IS NULL and usu.gus_codigo = gus.codigo and area.codigo (+)= usu.campo_n1  ";
		}

		// System.out.println(query);
		Statement stm = null;
		ResultSet rst = null;
		ArrayList<UserVO> users = new ArrayList<UserVO>();
		stm = conn.createStatement();
		rst = stm.executeQuery(query);

		while (rst.next()) {
			UserVO user = new UserVO();
			user.setCode(new Long(rst.getLong("codigo")));
			user.setName(rst.getString("nombre"));
			user.setDescription(rst.getString("descripcion"));
			user.setPassword(rst.getString("campo_a1"));
			user.setCodeArea(new Long(rst.getLong("campo_n1")));
			user.setEffectiveFrom(rst.getString("dvig"));
			user.setEffectiveUntil(rst.getString("hvig"));
			user.setUse(rst.getString("vigencia"));
			user.setUserGroupCode(new Long(rst.getLong("gus_codigo")));
			user.setCreationUser(rst.getString("usuario_crea"));
			user.setCreationTerminal(rst.getString("terminal_crea"));
			user.setCreationDate(rst.getString("fecha_crea"));
			user.setUpdateUser(rst.getString("usuario_modifica"));
			user.setUpdateTerminal(rst.getString("terminal_modifica"));
			user.setUpdateDate(rst.getString("fecha_modifica"));
			user.setUserGroupName(rst.getString("userGroupName"));
			user.setNameArea(rst.getString("areaName"));
			users.add(user);
		}
		rst.close();
		stm.close();
		return users;
	}

	public boolean createUser(UserVO user) throws SQLException {

		String strSQL = "";
		boolean result = false;
		PreparedStatement ps = null;
		strSQL = ""
				+ "INSERT INTO tcs_usuario ("
				+ "            codigo, nombre, descripcion, campo_a1, campo_n1, desde_vigencia, hasta_vigencia, "
				+ "            vigencia,  gus_codigo, vfl_codigo,"
				+ "            usuario_crea,terminal_crea, fecha_crea,usuario_modifica,"
				+ "             terminal_modifica,fecha_modifica) "
				+ "            VALUES(scs_usuario.nextval,?,?,?,?,to_date(?,'yyyy-mm-dd'),to_date(?,'yyyy-mm-dd'),?,?,2,?,?,?,?,?,?)";

		ps = conn.prepareStatement(strSQL);
		ps.setString(1, user.getName().toUpperCase());
		ps.setString(2, user.getDescription().toUpperCase());
		ps.setString(3, user.getPassword());
		ps.setString(4, (user.getCode()).toString());
		ps.setString(5, user.getEffectiveFrom());
		ps.setString(6, user.getEffectiveUntil());
		ps.setString(7, user.getUse().toUpperCase());
		ps.setLong(8, user.getUserGroupCode().longValue());
		ps.setString(9, user.getCreationUser().toUpperCase());
		ps.setString(10, user.getCreationTerminal());
		ps.setString(11, user.getCreationDate());
		ps.setString(12, user.getUpdateUser().toUpperCase());
		ps.setString(13, user.getUpdateTerminal());
		ps.setString(14, user.getUpdateDate());
		result = ps.execute();
		ps.close();
		return result;
	}

	public boolean updateUser(UserVO user) throws SQLException {

		String strSQL = "";
		boolean result = false;
		PreparedStatement ps = null;
		strSQL = "" + "UPDATE paoyer.tcs_usuario SET " + "           nombre=?,"
				+ "            descripcion=?," + "            campo_a1=?,"
				+ "            campo_n1=?,"
				+ "            desde_vigencia=to_date(?,'yyyy-mm-dd'),"
				+ "            hasta_vigencia=to_date(?,'yyyy-mm-dd'), "
				+ "            vigencia=?," + "            gus_codigo=?,"
				+ "            usuario_modifica=?,"
				+ "            terminal_modifica=?"
				+ "            where codigo=?";

		ps = conn.prepareStatement(strSQL);
		ps.setString(1, user.getName().toUpperCase());
		ps.setString(2, user.getDescription().toUpperCase());
		ps.setString(3, user.getPassword());
		ps.setString(4, (user.getCodeArea()).toString());
		ps.setString(5, user.getEffectiveFrom());
		ps.setString(6, user.getEffectiveUntil());
		ps.setString(7, user.getUse().toUpperCase());
		ps.setLong(8, user.getUserGroupCode().longValue());
		ps.setString(9, user.getUpdateUser().toUpperCase());
		ps.setString(10, "WEB=" + user.getUpdateTerminal());
		ps.setLong(11, user.getCode().longValue());
		result = ps.execute();
		ps.close();
		return result;
	}

	public boolean deleteUser(UserVO user) throws SQLException {

		String strSQL = "delete from tcs_usuario where codigo = ?";

		PreparedStatement ps = conn.prepareStatement(strSQL);

		ps.setLong(1, user.getCode().longValue());

		boolean update = ps.execute();
		ps.close();
		return update;

	}

	public boolean activateUser(UserVO user) throws SQLException {

		String strSQL = "update tcs_usuario set vigencia = ? where codigo = ?";

		PreparedStatement ps = conn.prepareStatement(strSQL);
		ps.setString(1, user.getUse());
		ps.setLong(2, user.getCode().longValue());

		boolean update = ps.execute();
		ps.close();
		return update;
	}

	public boolean desactivateUser(UserVO user) throws SQLException {

		String strSQL = "update tcs_usuario set vigencia = ? where codigo = ?";

		PreparedStatement ps = conn.prepareStatement(strSQL);
		ps.setString(1, user.getUse());
		ps.setLong(2, user.getCode().longValue());

		boolean update = ps.execute();
		ps.close();
		return update;
	}

	public UserVO userGroup(UserVO criterea) throws SQLException {

		String query = "SELECT grupo.codigo CODIGO "
				+ "FROM paoyer.TCS_GRUPO_USUARIO grupo,paoyer.TCS_USUARIO usuario "
				+ "WHERE usuario.GUS_CODIGO = grupo.CODIGO "
				+ "AND usuario.NOMBRE = '" + criterea.getName() + "'";
		Statement stm = null;
		ResultSet rst = null;
		stm = conn.createStatement();
		rst = stm.executeQuery(query);
		UserVO group = criterea;
		if (rst.next()) {
			group.setUserGroupCode(new Long(rst.getLong("codigo")));
		}
		return group;
	}

	public Vector<String> getResources(UserVO criterea) {
		Vector<String> resources = new Vector<String>();
		String query = "SELECT o1.CAMPO_A4 resources "
				+ "FROM TCS_OPCION o1, TCS_OPCIONES_GRUPO g "
				+ "WHERE g.opc_codigo = o1.codigo "
				+ "AND o1.tipo_opcion = 'W' " + "AND o1.CAMPO_A4 IS NOT NULL "
				+ "AND g.GUS_CODIGO = " + criterea.getUserGroupCode();
		Statement stm = null;
		ResultSet rst = null;
		try {
			stm = conn.createStatement();
			rst = stm.executeQuery(query);
			while (rst.next()) {
				resources.add(rst.getString("resources"));
			}
		} catch (SQLException e) {
			System.err.println("/******** TDSUB - cwsub");
			e.printStackTrace();
			System.err.println("TDSUB - cwsub ********/");
		}
		return resources;
	}

	public String getMenu(String strUserGroup, String strpath) {
		MenuDAO mn = new MenuDAO(strUserGroup, conn, strpath);
		return mn.getMenu();
	}
}